package edu.egypt.helpers;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import edu.egypt.model.Permission;

public class PermissionsDbOperations {

	private static PermissionsDbOperations instance = null;
	private static Connection conn= null;
	
	private PermissionsDbOperations(){
		// making only constructor as private so that no one can get instance other than getInstance method
	}
	
	public static synchronized PermissionsDbOperations getSingletonInstance(){
		
		if( instance == null){
			instance = new PermissionsDbOperations();
		}
		
		return instance;
		
	}
	
	public ArrayList<Permission> getAllPermissions(){
		
		ArrayList<Permission> result = new ArrayList<>();
		try{
			Statement stmt = getDbConnection().createStatement();
			String Query = "SELECT * From permission";
			ResultSet rs = stmt.executeQuery(Query);
			
			while(rs.next()){
				Permission per = new Permission(rs.getInt("ID"),rs.getString("Name"),rs.getString("Description"));
				result.add(per);
			}
			// get all permissions as resultset and then convert them into arrayist of permissions..
		}catch(SQLException ex){
			
		}
		return result;
		
	}
	public ArrayList<Integer> getPermissionByRole(int id){
		ArrayList<Integer> permissions = new ArrayList<>();
		try{
			PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("SELECT Permission_ID FROM role_permission WHERE Role_ID = ? ");
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				permissions.add(rs.getInt("Permission_ID"));
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}
		return permissions;
	}
	private Connection getDbConnection(){
		
		if ( conn == null){
			
			try{
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://localhost/ibm", "root", "");
			}catch(ClassNotFoundException | SQLException ex){
				ex.printStackTrace();
			}
		}
		
		return conn;
	}

	public void editPermission(int id, String name , String desc){
		try{
			PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("UPDATE permission set Name = ?, Description = ? WHERE ID = ? ");
			
			st.setString(1, name);
			st.setString(2, desc);
			st.setInt(3, id);
			
			st.executeUpdate();
		}catch(SQLException ex){
			ex.printStackTrace();
		}
	}
	
	public void addPermission(String name , String desc){
		
		try{
			PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("INSERT INTO permission (Name,Description) VALUES(?,?) ");
			st.setString(1, name);
			st.setString(2, desc);
			
			st.execute();
		}catch(SQLException ex){
			ex.printStackTrace();
		}
	}
	
	public void deletePermission(int id){
		
		try{
			
			PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("DELETE FROM permission WHERE ID = ? ");
			st.setInt(1,id);
			
			st.execute();
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}
	}
	
}
